#MySQL SQL转换

EXEC DBMS_SCHEDULER.CREATE_JOB (
    job_name IN VARCHAR,   -- 对应字段  EVENT_NAME
    job_type IN VARCHAR,    -- 对应字段  EVENT_TYPE
    job_action IN VARCHAR , -- 对应字段   begin  EVENT_DEFINITION end ;
    number_of_arguments IN INTEGER DEFAULT 0,   -- 无对应字段  保留字段，使用缺省值 0。
    start_date IN TIMESTAMP DEFAULT NULL,   -- 对应字段    STARTS /  EXECUTE_AT
    repeat_interval IN VARCHAR DEFAULT NULL,    -- 对应字段 INTERVAL_VALUE INTERVAL_FIELD
    end_date IN TIMESTAMP DEFAULT NULL, -- 对应字段   ENDS
    job_class IN VARCHAR DEFAULT 'DEFAULT_JOB_CLASS',   -- 无对应字段  保留字段，使用缺省值   'DEFAULT_JOB_CLASS'。
    enabled IN BOOLEAN DEFAULT FALSE,   -- 对应字段  STATUS
    auto_drop IN BOOLEAN DEFAULT TRUE,  -- 对应字段   ON_COMPLETION
    comments IN VARCHAR DEFAULT NULL);  -- 对应字段  EVENT_COMMENT

 -- MySQL
 CREATE TEMPORARY TABLE x(id INT)
 -- YashanDB22.2预期结果
 CREATE GLOBAL TEMPORARY TABLE "X" ("ID" INT);
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 CREATE TABLE MYL.TEST4 (col1 INT REFERENCES MYL.T2(col1) ON
    DELETE
    SET
    null,
    col2 INT DEFAULT 1 CHECK(col2 < 30));
 -- YashanDB22.2预期结果
 CREATE TABLE "MYL"."TEST4" (
    "COL1" INT,
    "COL2" INT DEFAULT 1
);
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
CREATE TABLE x(id INT auto_increment DEFAULT 'xxx' COMMENT 'xxx');
 -- YashanDB22.2预期结果
 CREATE SEQUENCE "X_ID_SEQ" INCREMENT BY 1 START WITH 1;
CREATE TABLE "X" (
    "ID" INT DEFAULT 'xxx' DEFAULT "X_ID_SEQ".NEXTVAL
);
 
COMMENT ON COLUMN "X"."ID" IS 'xxx';
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
CREATE TABLE x(id INT auto_increment DEFAULT 'xxx' COMMENT 'xxx') COMMENT 'xxx' auto_increment = 1231 TABLESPACE xxx STORAGE DISK;
 -- YashanDB22.2预期结果
 CREATE SEQUENCE "X_ID_SEQ" INCREMENT BY 1 START WITH 1231;
CREATE TABLE x (
    "ID" INT DEFAULT 'xxx' DEFAULT "X_ID_SEQ".NEXTVAL
) TABLESPACE "XXXDISK";
 
COMMENT ON COLUMN "X"."ID" IS 'xxx';
COMMENT ON TABLE "X" IS 'xxx';
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 CREATE TABLE x(id INT auto_increment DEFAULT 'xxx' COMMENT 'xxx') COMMENT 'xxx' auto_increment = 1231 TABLESPACE xxx STORAGE DISK;
 -- YashanDB22.2预期结果
 CREATE SEQUENCE "X_ID_SEQ" INCREMENT BY 1 START WITH 1231;
CREATE TABLE "x" (
    "ID" INT DEFAULT 'xxx' DEFAULT "X_ID_SEQ".NEXTVAL
) TABLESPACE "XXXDISK";
 
COMMENT ON COLUMN "X"."ID" IS 'xxx';
COMMENT ON TABLE "X" IS 'xxx';
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 	
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/var/appdata/96/data'
    INDEX DIRECTORY = '/var/appdata/96/idx' TABLESPACE  xxx,
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/var/appdata/97/data'
    INDEX DIRECTORY = '/var/appdata/97/idx',
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/var/appdata/98/data'
    INDEX DIRECTORY = '/var/appdata/98/idx'
);
 -- YashanDB22.2预期结果
 CREATE TABLE "TH" (
    "ID" INT,
    "NAME" VARCHAR(30 CHAR),
    "ADATE" DATE
)
PARTITION BY LIST (YEAR("ADATE")) ( PARTITION "P1999" VALUES ((1995), (1999), (2003)) , PARTITION "P2000" VALUES ((1996), (2000), (2004))
    TABLESPACE "XXX", PARTITION "P2001" VALUES ((1997), (2001), (2005)) , PARTITION "P2002" VALUES ((1998), (2002), (2006))
);
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
 -- YashanDB22.2预期结果
 CREATE TABLE "TS" (
    "ID" INT,
    "PURCHASED" DATE
)
PARTITION BY RANGE (YEAR("PURCHASED"))
 ( PARTITION "P0" VALUES LESS THAN (1990) , PARTITION "P1" VALUES LESS THAN (2000) , PARTITION "P2" VALUES LESS THAN (MAXVALUE) );
 -- YashanDB23.1预期结果
 CREATE TABLE "TS" (
    "ID" INT,
    "PURCHASED" DATE
)
PARTITION BY RANGE (YEAR("PURCHASED"))
SUBPARTITION BY HASH(TO_DAYS("PURCHASED")) SUBPARTITIONS  2
 ( PARTITION "P0" VALUES LESS THAN (1990) , PARTITION "P0" VALUES LESS THAN (2000) , PARTITION "P2" VALUES LESS THAN (MAXVALUE) );
 -- 同上

 -- MySQL
CREATE TABLE `test_timestamp`(
    id INT , 
    t1 TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3))
 -- YashanDB22.2预期结果
CREATE TABLE "TEST_TIMESTAMP" (
	"ID" INT,
	"T1" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP()
)
;
CREATE TRIGGER "TEST_TIMESTAMP_T1_TRI"
BEFORE UPDATE ON "TEST_TIMESTAMP"
FOR EACH ROW
 BEGIN
   IF NOT UPDATING('T1')  THEN 
   		:NEW."T1" := CURRENT_TIMESTAMP();
   END IF;
END;/
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 CREATE OR replace  VIEW v3 (idalias ) AS SELECT id FROM v1 WHERE a > 0
 -- YashanDB22.2预期结果
 CREATE OR REPLACE VIEW "V3"
AS
SELECT "ID"
FROM "V1"
WHERE "A" > 0;
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 CREATE OR replace  VIEW v3 (idalias ) AS SELECT id FROM v1 WHERE a > 0
WITH CASCADED CHECK OPTION;
 -- YashanDB22.2预期结果
 CREATE OR replace  VIEW "V3" ("IDALIAS" ) AS SELECT "ID" FROM "V1" WHERE "A" > 0

 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 /*The 1 SQL statement*/
CREATE UNIQUE INDEX xxx ON x(x);
 
/*The 2 SQL statement*/
CREATE FULLTEXT INDEX xxx ON x(x);
 
/*The 3 SQL statement*/
CREATE INDEX xxx ON x(x);

 -- YashanDB22.2预期结果
 /*The 1 SQL statement*/
CREATE UNIQUE INDEX "XXX" ON "X" ("X");
 
/*The 2 SQL statement*/
CREATE  INDEX "XXX" ON "X" ("X");
 
/*The 3 SQL statement*/
CREATE  INDEX "XXX" ON "X" ("X");
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 /*The 1 SQL statement*/
CREATE INDEX id_index ON lookup (id) USING BTREE;
 
/*The 2 SQL statement*/
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

 -- YashanDB22.2预期结果
 /*The 1 SQL statement*/
CREATE INDEX "ID_INDEX" ON "LOOKUP" ("ID");
 
/*The 2 SQL statement*/
CREATE INDEX "ID_INDEX" ON "T1" ("ID");
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 ALTER TABLE xx ADD PRIMARY USING BTREE (id);
 -- YashanDB22.2预期结果
 ALTER TABLE "XX" ADD PRIMARY KEY ("ID");
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 ALTER TABLE student ADD CONSTRAINT  xx UNIQUE KEY USING BTREE(id)  ;
 -- YashanDB22.2预期结果
 ALTER TABLE "STUDENT" ADD CONSTRAINT "XX" UNIQUE ("ID");
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
ALTER TABLE student ADD CHECK (id>0) ENFORCED ;
 -- YashanDB22.2预期结果
 ALTER TABLE "STUDENT" ADD CHECK ("ID">0) ;
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 ALTER TABLE X MODIFY ID INT NOT NULL;
 -- YashanDB22.2预期结果
 ALTER TABLE "X" MODIFY "ID"  NOT NULL;
 -- YashanDB23.1预期结果
 -- 同上

 -- MySQL
 CREATE DEFINER=`root`@`%` EVENT `myevent` 
    ON SCHEDULE AT '2024-01-09 06:14:00' 
    ON COMPLETION PRESERVE DISABLE ON SLAVE 
    DO 
        UPDATE mytable SET mycol = mycol + 1;
    ;   
 -- YashanDB22.2预期结果
 EXEC DBMS_SCHEDULER.CREATE_JOB('LSP.myevent',
    'PLSQL_BLOCK', 
    ' BEGIN UPDATE mytable SET mycol = mycol + 1 ; END ;' ,
    0,
    TO_TIMESTAMP('2024-01-09 06:14:00', 'YYYY-MM-DD HH24:MI:SS'),
    null,
    null,
    'DEFAULT_JOB_CLASS',
    false,
    false,
    null);
 -- YashanDB23.1预期结果
 -- 同上